﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CLEX.DataContracts;

namespace CLEX.Server.DAL
{
    public sealed class FriendRequestDAL : IFriendRequestDAL
    {
        private const string FROM_USER_ID_COL = "from_user_id";
        private const string TO_USER_ID_COL = "to_user_id";

        private const string GET_ALL_SP = "friend_request_get_all";
        private const string GET_SP = "friend_request_get";
        private const string CREATE_SP = "friend_request_create";
        private const string UPDATE_SP = "friend_request_update";
        private const string DELETE_SP = "friend_request_delete";

        private readonly string connectionString;

        public FriendRequestDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public List<FriendRequestData> GetAll()
        {
            List<FriendRequestData> result = new List<FriendRequestData>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_ALL_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int fromUserIdCol = reader.GetOrdinal(FROM_USER_ID_COL);
                        int toUserIdCol = reader.GetOrdinal(TO_USER_ID_COL);

                        while (reader.Read())
                        {
                            result.Add(new FriendRequestData()
                            {
                                FromUserId = reader.GetInt32(fromUserIdCol),
                                ToUserId = reader.GetInt32(toUserIdCol),
                            });
                        }
                    }
                }
            }

            return result;
        }

        public FriendRequestData Get(int fromUserId, int toUserId)
        {
            FriendRequestData result;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(FROM_USER_ID_COL, fromUserId);
                    cmd.Parameters.AddWithValue(TO_USER_ID_COL, toUserId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int fromUserIdCol = reader.GetOrdinal(FROM_USER_ID_COL);
                        int toUserIdCol = reader.GetOrdinal(TO_USER_ID_COL);

                        reader.Read();
                        result = new FriendRequestData()
                        {
                            FromUserId = reader.GetInt32(fromUserIdCol),
                            ToUserId = reader.GetInt32(toUserIdCol),
                        };
                    }
                }
            }

            return result;
        }

        public FriendRequestData Create(FriendRequestData friendRequest)
        {
            FriendRequestData result = new FriendRequestData();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(CREATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(FROM_USER_ID_COL, friendRequest.FromUserId);
                    cmd.Parameters.AddWithValue(TO_USER_ID_COL, friendRequest.ToUserId);
                    cmd.ExecuteNonQuery();
                }
            }

            result.FromUserId = friendRequest.FromUserId;
            result.ToUserId = friendRequest.ToUserId;
            return result;
        }

        public void Update(FriendRequestData friendRequest)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(UPDATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(FROM_USER_ID_COL, friendRequest.FromUserId);
                    cmd.Parameters.AddWithValue(TO_USER_ID_COL, friendRequest.ToUserId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void Delete(FriendRequestData friendRequest)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(DELETE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(FROM_USER_ID_COL, friendRequest.FromUserId);
                    cmd.Parameters.AddWithValue(TO_USER_ID_COL, friendRequest.ToUserId);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
